using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Data.OleDb;
using System.Web;
using System.Collections.Generic;
namespace gcLibAdmin
{
	/// <summary>
	/// Summary description for Helper.
	/// </summary>
	public class Helper
	{
		public Helper()
		{
			//
			// TODO: Add constructor logic here
			//
		}

        static public int ExecSQL(string sql, out int curCount)
        {
            curCount = -1;
            int bReturn = 1;
            using (SqlConnection connection = new SqlConnection(getSqlStr()))
            {
                connection.Open();

                SqlCommand command = connection.CreateCommand();
                SqlTransaction transaction;

                // Start a local transaction.
                transaction = connection.BeginTransaction(
                    IsolationLevel.ReadCommitted, "SampleTransaction2");

                // Must assign both transaction object and connection
                // to Command object for a pending local transaction.
                command.Connection = connection;
                command.Transaction = transaction;

                try
                {
                    command.CommandText = sql;
                    command.ExecuteNonQuery();

                    transaction.Commit();
                }
                catch (Exception e)
                {
                    try
                    {
                        transaction.Rollback();
                    }
                    catch (SqlException ex)
                    {
                        if (transaction.Connection != null)
                        {
                            bReturn = -1;
                        }
                    }

                    bReturn = -2;
                }
            }
            return bReturn;
        }

        static public int ExecSQLArray(List<string> sqlArray, out int curCount)
        {
            curCount = -1;
            int bReturn = 1;
            using (SqlConnection connection = new SqlConnection(getSqlStr()))
            {
                connection.Open();

                SqlCommand command = connection.CreateCommand();
                SqlTransaction transaction;

                // Start a local transaction.
                transaction = connection.BeginTransaction(
                    IsolationLevel.ReadCommitted, "SampleTransaction2");

                // Must assign both transaction object and connection
                // to Command object for a pending local transaction.
                command.Connection = connection;
                command.Transaction = transaction;

                try
                {
                    for (int j = 0; j < sqlArray.Count; j++)
                    {
                        command.CommandText = sqlArray[j];
                        command.ExecuteNonQuery();
                    }
                    transaction.Commit();
                }
                catch (Exception e)
                {
                    try
                    {
                        transaction.Rollback();
                    }
                    catch (SqlException ex)
                    {
                        if (transaction.Connection != null)
                        {
                            bReturn = -1;
                        }
                    }

                    bReturn = -2;
                }
            }
            return bReturn;
        }

        static public int GetNewValue(string gobaleName, string tablename, string type, out int curCount)
        {
            gobaleName = gcSupport.ProcessFilter(gobaleName);
            tablename = gcSupport.ProcessFilter(tablename);

            string date = DateTime.Now.Year.ToString();
            curCount = -1;
            int bReturn = 1;
            using (SqlConnection connection = new SqlConnection(getSqlStr()))
            {
                connection.Open();

                SqlCommand command = connection.CreateCommand();
                SqlTransaction transaction;
                SqlDataReader rd = null;

                // Start a local transaction.
                transaction = connection.BeginTransaction(
                    IsolationLevel.ReadCommitted, "SampleTransaction");

                // Must assign both transaction object and connection
                // to Command object for a pending local transaction.
                command.Connection = connection;
                command.Transaction = transaction;

                try
                {
                    command.CommandText = "Update " + gobaleName + " SET Type='" + type + "'" + " WHERE Name='" + tablename + "'";
                    command.ExecuteNonQuery();
                    if (type.Contains("Date"))
                    {
                        string tmpDate = DateTime.Now.Month +"/"+DateTime.Now.Day +"/"+DateTime.Now.Year;
                        command.CommandText = "Update " + gobaleName + " SET Thoigian='" + tmpDate + "', CurCount=0 WHERE year(Thoigian)!=" + date + " AND Name='" + tablename + "'"; ;
                        command.ExecuteNonQuery();

                        command.CommandText = "Update " + gobaleName + " SET CurCount=CurCount+1 WHERE year(Thoigian)='" + date + "' AND Name='" + tablename + "'"; ;
                        command.ExecuteNonQuery();
                    }
                    else if (type.Contains("Normal"))
                    {
                        command.CommandText = "Update " + gobaleName + " SET CurCount=CurCount+1 WHERE Name='" + tablename + "'"; ;
                        command.ExecuteNonQuery();
                    }

                    command.CommandText = "SELECT CurCount FROM " + gobaleName + " WHERE Name='" + tablename + "'"; ;
                    curCount = (int)command.ExecuteScalar();
                    transaction.Commit();
                }
                catch (Exception e)
                {
                    try
                    {
                        transaction.Rollback();
                    }
                    catch (SqlException ex)
                    {
                        if (transaction.Connection != null)
                        {
                            bReturn = -1;
                        }
                    }

                    bReturn = -2;
                }
            }
            return bReturn;
        }

        public static DataTable GetDataTableNew(string query)
        {
            string v = getOleStr();
            DataTable myDataTable = new DataTable();
            using (OleDbConnection connection =
                    new OleDbConnection(getOleStr()))
            {
                OleDbDataAdapter MyOleDbDataAdapter = new OleDbDataAdapter();
                MyOleDbDataAdapter.SelectCommand = new OleDbCommand(query, connection);

                try 
                {
                    connection.Open();
                    MyOleDbDataAdapter.Fill(myDataTable); 
                }
                finally { ; }
                
            }
            return myDataTable;
        }

        public static string getOleStr()
        {

            //for 2005
            //string path = "Provider=SQLNCLI;Data Source=DINHHUNG\\SQLEXPRESS;Database=VTCM;Uid=sa;Pwd=sa";
            //for 2000
            string path = "Provider=SQLOLEDB.1;Data Source=HUYVTQPC;Initial Catalog=NhaHang;User ID=sa;Password=50501101";
            //string path = "Provider=SQLOLEDB.1;Initial Catalog=QuanGio_Data";
            return path;
        }
        public static string getSqlStr()
        {
            return ConfigurationManager.ConnectionStrings["NhaHangConnectionString"].ConnectionString;

        }

        public static string getUploadLogoFolder()
        {
            return ConfigurationSettings.AppSettings["Upload"];
        }
		public static string getConfigValue(string name)
		{
			return ConfigurationSettings.AppSettings[name];
		}

		public static void setConfigValue(string name, string value)
		{
			if (ConfigurationSettings.AppSettings[name]!=null)
				ConfigurationSettings.AppSettings[name]=value;
		}
       
		/**
		 * Des: Thuc thi cau truy van va tra ve doi tuong chua result. Tu dong dong cau noi.
		 *		ErrorCode duoc gan bang 1 neu viec kiem tra thanh cong, nguoc lai errorCode se co
		 *      gia tri bang 0.
		 */
		
		/**
		 * Des: Thuc hien danh sach cac lenh khong truy van theo thu tu.
		 *		Tra ve true neu tat ca cac lenh deu thanh cong, nguoc lai tra ve false
		 *		va cac lenh se duoc rollback neu co the.
		 */
		public static bool doTransactionNonQuery(string[] list_of_sql, string conStr)
		{
			SqlConnection myCon = new SqlConnection(conStr);
			myCon.Open();

			// Start a local transaction.
			SqlTransaction myTrans = myCon.BeginTransaction();

			// Enlist the command in the current transaction.
			SqlCommand myCmd = myCon.CreateCommand();
			myCmd.Transaction = myTrans;

			try
			{
				for (int i=0; i<list_of_sql.Length; i++)
				{
					myCmd.CommandText = list_of_sql[i];
					myCmd.ExecuteNonQuery();
				}
				myTrans.Commit();
				return true;
			}
			catch(Exception e)
			{
				try
				{
					myTrans.Rollback();
				}
				catch (SqlException ex)
				{
                    string strE = ex.ToString();
				}
                string strU = e.ToString();
			}
			finally
			{
				myCon.Close();
			}
			return false;
		}

        public static bool checkExist(string val, string colname, string tablename, out int errorCode)
        {
            colname = gcSupport.ProcessFilter(colname);
            tablename = gcSupport.ProcessFilter(tablename);
            string strSql = "select " + colname
                + " from " + tablename
                + " where " + colname + "=N'"
                + val.Replace("'", "''") + "';";
            
            bool bReturn = false;
            errorCode = -1;

            using (SqlConnection connection = new SqlConnection(getSqlStr()))
            {
                connection.Open();

                SqlCommand command = connection.CreateCommand();
                SqlTransaction transaction;

                // Start a local transaction.
                transaction = connection.BeginTransaction(
                    IsolationLevel.ReadCommitted, "SampleTransaction2");

                // Must assign both transaction object and connection
                // to Command object for a pending local transaction.
                command.Connection = connection;
                command.Transaction = transaction;

                try
                {
                    command.CommandText = strSql;
                    SqlDataReader rd = command.ExecuteReader();
                    if (rd.HasRows == true)
                    {
                        bReturn = true;
                        errorCode = 0;
                    }
                    transaction.Commit();
                    
                }
                catch (Exception e)
                {
                    try
                    {
                        transaction.Rollback();
                    }
                    catch (SqlException ex)
                    {
                        if (transaction.Connection != null)
                        {
                            bReturn = false;
                        }
                    }

                    bReturn = false;
                }
            }
            errorCode = 0;
            return bReturn;
        }

		/**
		 * Des: Tra ve so hang ket qua thoa cau truy van.
		 *      Neu truy van thuc hien thanh cong errorCode=1, nguoc lai = 0.
		 */
        public static bool ExecuteNonQuery(string sql, string strConnect)
        {
            bool bReturn = false;

            using (SqlConnection connection = new SqlConnection(getSqlStr()))
            {
                connection.Open();

                SqlCommand command = connection.CreateCommand();
                SqlTransaction transaction;

                // Start a local transaction.
                transaction = connection.BeginTransaction(
                    IsolationLevel.ReadCommitted, "SampleTransaction2");

                // Must assign both transaction object and connection
                // to Command object for a pending local transaction.
                command.Connection = connection;
                command.Transaction = transaction;

                try
                {
                    command.CommandText = sql;
                    command.ExecuteNonQuery();
                   
                    transaction.Commit();
                    bReturn = true;
                }
                catch (Exception e)
                {
                    try
                    {
                        transaction.Rollback();
                    }
                    catch (SqlException ex)
                    {
                        if (transaction.Connection != null)
                        {
                            bReturn = false;
                        }
                    }

                    bReturn = false;
                }
            }
            return bReturn;
        }

		// valList: separate by comma
		public static bool deleteAll(string valList, string colname, string tablename)
		{
			string strSql = "delete " + tablename
				+ " where " + colname + " in ('" + valList.Replace("'","''").Replace(",","','") + "')";
            return ExecuteNonQuery(strSql, getOleStr());
		}

		public static bool deleteAll(ArrayList valList, string colname, string tablename)
		{
			// make list
			string list = "";
			for (int i=0; i<valList.Count; i++)
				list += "," + valList[i];
			if (!list.Equals("")) 
			{
				list = list.Substring(1);
				string strSql = "delete " + tablename
					+ " where " + colname + " in ('" + list.Replace("'","''").Replace(",","','") + "')";
                return ExecuteNonQuery(strSql, getOleStr());
			}
			return false;
		}

        public static bool deleteAllData(string tablename, string strFilter)
        {
            string strSql = "delete from " + tablename + (strFilter.Equals("")? "": " WHERE " + strFilter);
            return ExecuteNonQuery(strSql, getOleStr());
        }

        
        public static void upload(System.Web.UI.HtmlControls.HtmlInputFile ctrlname, string dir_from_root, string filename, System.Web.HttpRequest request)
        {
            string path = request.ServerVariables["APPL_PHYSICAL_PATH"];
            path = path + dir_from_root + filename;
            ctrlname.PostedFile.SaveAs(path);
        }
        public static List<object[]> gcGetObjectWS(string prefixText, int count, string objname, string field, 
                                    string filter, object obj, bool bCheckFilter)
        {
            
            List<object[]> list = new List<object[]>();
            string text = (HttpContext.Current.Session["FlushText"] == null) ? "-1" : (string)HttpContext.Current.Session["FlushText"];
            string flag = (HttpContext.Current.Session["isFlush"] == null) ? "" : (string)HttpContext.Current.Session["isFlush"];
            if (flag == "true")
            {
                if (text.Length > 0)
                {
                    if (prefixText.Contains(text))
                        return list;
                }
            }
            else if (flag == "false")
            {
                if((prefixText!=null) && (prefixText.Length>0))
                if (prefixText == text)
                {
                    string objnameSes = (HttpContext.Current.Session["FlushObject"] == null) ? "-1" : (string)HttpContext.Current.Session["FlushObject"];
                    if (objnameSes.Equals(objname))
                    {
                        list = (List<object[]>)HttpContext.Current.Session["FlushTextData"];
                        return list;
                    }
                }
            }
            //----------------------------------------------------------------
            string sqlCountNew="";
            string sql = gcGetSQL(prefixText, count, objname, field, filter, bCheckFilter, ref sqlCountNew, 1);

            DataTable myDataTable = Helper.GetDataTableNew(sql);
            if (myDataTable != null)
            {
                if (myDataTable.Rows.Count <= 0)
                {
                    prefixText = prefixText.Substring(0, prefixText.Length - 1);
                    sql = gcGetSQL(prefixText, count, objname, field, filter, bCheckFilter, ref sqlCountNew, 1);
                    myDataTable = Helper.GetDataTableNew(sql);
                }
            }
            else
            {
                prefixText = prefixText.Substring(0, prefixText.Length - 1);
                sql = gcGetSQL(prefixText, count, objname, field, filter, bCheckFilter, ref sqlCountNew, 1);
                myDataTable = Helper.GetDataTableNew(sql);
            }
            string[] arrField = field.Split(',');
            int index = sqlCountNew.IndexOf("from");
            string sqlCount = "select count(*) as c " + sqlCountNew.Substring(index, sqlCountNew.Length - index);
            DataTable db = Helper.GetDataTableNew(sqlCount);
            //------------------------------------------------------------
            for (int i = 0; i < myDataTable.Rows.Count; i++)
            {
                object[] item = new object[arrField.Length + 2];
                item[0] = myDataTable.Rows[i][0].ToString();
                for (int m = 0; m < arrField.Length; m++)
                    item[m+1] = myDataTable.Rows[i][m+1].ToString();
                item[arrField.Length+1] = 0;
                list.Add(item);
            }
            if (myDataTable.Rows.Count == 1)
            {
                string id = (list[0][0]).ToString();
                if ( (filter != null) && (filter.Trim().Length > 1))
                    filter += " AND (Id<>" + id + ")";
                else
                    filter += " (Id<>" + id + ")";
                list[0][arrField.Length+1] = 1;
                sql = gcGetSQL(prefixText, count-1, objname, field, filter, false, ref sqlCountNew, -1);
                DataTable dbr = Helper.GetDataTableNew(sql);
                for (int i = 0; i < dbr.Rows.Count; i++)
                {
                    object[] item = new object[arrField.Length + 2];
                    item[0] = dbr.Rows[i][0].ToString();
                    for (int m = 0; m < arrField.Length; m++)
                        item[m + 1] = dbr.Rows[i][m + 1].ToString();
                    item[arrField.Length+1] = 0;
                    list.Add(item);
                }
            }
            if (db.Rows.Count > 0)
            {
                object[] item = new object[1];
                item[0] = db.Rows[0]["c"];
                list.Add(item);
            }
            if (myDataTable.Rows.Count > 0)
            {
                 
                HttpContext.Current.Session["FlushTextData"] = list;
                HttpContext.Current.Session["FlushText"] = prefixText;
                HttpContext.Current.Session["FlushObject"] = objname;
                HttpContext.Current.Session["isFlush"] = "false";
            }
            else
            {
                HttpContext.Current.Session["FlushText"] = prefixText;
                HttpContext.Current.Session["isFlush"] = "true";
            }
            return list;
        }
        //--------------------------------------------------------------------------------
        public static string gcGetSQL(string prefixText, int count, string objname, 
                                        string field, string filter, bool bCheckFilter,ref string sqlCount, int bLike)
        {
            //security
            if(bCheckFilter)
                filter = gcSupport.ProcessFilter(filter);
            prefixText = gcSupport.ProcessSimpleFilter(prefixText);
            //end security
            string[] arrField = field.Split(',');
            if (arrField.Length < 1)
                arrField = new string[] { field };
            string sql = String.Format("Select TOP {0} Id,{1}   from {2}", count, field, objname);
            if (prefixText == null)
                sql = String.Format("Select TOP {0} Id,{1}   from {2}", count, field, objname);
            else
            {
                prefixText = prefixText.Trim();
                if (prefixText.Length < 1)
                {
                    sql = String.Format("Select TOP {0} Id,{1}   from {2}", count, field, objname);
                    if ( (filter != null) && (filter.Trim().Length>0))
                    {
                        sql += " WHERE " + filter;
                    }
                    sqlCount = sql;
                }
                else
                {
                    sql = String.Format("Select TOP {0} Id,{1}   from {2}", count, field, objname);
                    sqlCount = sql;
                    string temp = "";
                    if (bLike >= 0)
                    {
                        for (int j = 0; j < arrField.Length; j++)
                        {
                            if (j == 0)
                                temp += String.Format("({0} Like N'%{1}%')", arrField[j], prefixText);
                            else
                                temp += String.Format(" OR ({0} Like N'%{1}%')", arrField[j], prefixText);
                        }
                    }
                    if (temp.Length > 0)
                        sql += " WHERE " + temp;

                    if (filter != null)
                    {
                        if (filter.Trim().Length > 1)
                        {
                            if (temp.Length > 0)
                            {
                                sql += " AND " + filter;
                                sqlCount += " WHERE " + filter;
                            }
                            else
                            {
                                sql += " WHERE " + filter;
                                sqlCount += " WHERE " + filter;
                            }
                        }
                    }
                }
            }
            return sql;
        }
	}
}
